-- the search quesry in a procedure 
-- NEEDS TO BE CHECKED PROPERLY ONCE THE MIGRATION DATA ARE WORKING!
CREATE OR REPLACE FUNCTION search(search_text TEXT, account_id INTEGER)
RETURNS TABLE (
	id INT,
	title TEXT,
	body TEXT,
  author_id INT,
	accepted_answer_id INT,
	closed_date TIMESTAMP,
	creation_date TIMESTAMP,
	score INT
)
AS $$
BEGIN
	
INSERT INTO search(search_text, search_date, account_id) VALUES(search_text, NOW() AT TIME ZONE 'UTC', account_id);

RETURN QUERY SELECT DISTINCT question.id, question.title, question.body, author.id, question.accepted_answer_id, question.closed_date, question.creation_date, question.score
FROM question 
JOIN author ON question.author_id=author.id
JOIN answer ON answer.question_id=question.id
JOIN comment ON comment.question_id=question.id OR comment.answer_id=answer.id
WHERE LOWER(question.title) LIKE LOWER('%' || search_text || '%')
	OR LOWER(question.body) LIKE LOWER('%' || search_text || '%');
--    OR LOWER(answer.body) LIKE LOWER('%' || search_text || '%')
  --  OR LOWER(comment.text) LIKE LOWER('%' || search_text || '%');
END;
$$
LANGUAGE 'plpgsql';

-- sign up a user
CREATE OR REPLACE FUNCTION sign_up(name TEXT, email TEXT, password CHARACTER VARYING(60))
RETURNS VOID
AS $$
INSERT INTO account(name, email, password) VALUES(sign_up.name, sign_up.email, sign_up.password);	
$$
LANGUAGE SQL;


-- mark and annotate a question
CREATE OR REPLACE FUNCTION mark_question(account_id INTEGER, question_id INTEGER, annotation TEXT DEFAULT NULL)
RETURNS VOID
AS $$
INSERT INTO marked_question(account_id, question_id, annotation) VALUES(mark_question.account_id, mark_question.question_id, mark_question.annotation)
ON CONFLICT(account_id, question_id) DO UPDATE 
	SET annotation=mark_question.annotation;	
$$
LANGUAGE SQL;
